package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.City;

public class DBCity {

	private  Connection con;
	/** Creates a new instance of DBity */
	public DBCity() {
		con = DbConnection1.getInstance().getDBcon();
	}

	public City findCity(int zipCode, boolean retriveAssociation) {
		String wClause = "  zipCode = '" + zipCode + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	private ArrayList<City> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<City> list = new ArrayList<City>();	

		String query =  buildQuery(wClause);

		try{ // read the City from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				City cityObj = new City();
				cityObj = buildCity(results);	
				list.add(cityObj);	
			}//end while
			stmt.close();     
	//		if(retrieveAssociation)
	//		{   //The city is to be build as well
	//			for(City cityObj : list){

	//			}
	//		}//end if   

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	//Singlewhere is used when we only select one City 	
	private City singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		City cityObj = new City();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the City from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				cityObj = buildCity(results);
				//assocaition is to be build
				stmt.close();
				if(retrieveAssociation)
				{   
				}
			}
			else{ //no City was found
				cityObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return cityObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT *  FROM City";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an City object
	private City buildCity(ResultSet results)
	{   City cityObj = new City();
	try{ // the columns from the table city  are used
		cityObj.setZipcode(results.getInt("zipCode"));
		cityObj.setCityName(results.getString("cityName"));
	}
	catch(Exception e)
	{
		System.out.println("error in building the City object");
	}
	return cityObj;
	}
}
